#!/usr/bin/env python

import sqlite3
import os
import run
import time

def sql_getConn():
    """
	get sqlite connection
	"""
	return sqlite3.connect('data/log.db3')

def sql_getCur(conn):
	"""
	get sqlite operating cursor
	"""
	return conn.cursor()

def sql_isURLExist(cur, targetArgs):
	"""
	find if the url had been existed
	"""
	cur.execute("SELECT * FROM cs_urls WHERE csurl='"+targetArgs.u+"' OR csip='"+targetArgs.i+"'")
	last_checked_data = cur.fetchone()
	if (last_checked_data != None):
		next_do = raw_input('This URL('+targetArgs.u+') or IP('+targetArgs.i+') has been checked at ['+last_checked_data[3]+']\nWhat you want to do next? (C)ontinue or (Q)uit: ')
		if (next_do=='Q' or next_do=='q'):
			return -1
		else :
			return last_checked_data
	return 1

def sql_insertURL(conn, cur, targetArgs):
	"""
	insert the set to sqlite
	"""
	cur.execute("INSERT INTO cs_urls (`csurl`, `csip`, `cstime`) VALUES ('"+targetArgs.u+"', '"+targetArgs.i+"', '"+time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))+"')")
	conn.commit()
	return cur.lastrowid

def sql_updateURL(conn, cur, targetArgs):
	"""
	update the set to sqlite
	"""
	cur.execute("UPDATE cs_urls SET `csurl`='"+targetArgs.u+"' and `csip`='"+targetArgs.i+"' and `cstime`='"+time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))+"' WHERE csurl='"+targetArgs.i+"'")
	conn.commit()
	return cur.lastrowid

def sql_createCSTable(conn, cur, targetArgs):
	"""
	create table to save the cscan result
	"""
	addr_dict = targetArgs.i.split('.')
	tableName = 'CSResult_'+addr_dict[0]+'_'+addr_dict[1]+'_'+addr_dict[2]

	sql_check = "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='"+tableName+"'"
	cur.execute(sql_check)
	conn.commit()
	isExisted = cur.fetchone()

	if (isExisted[0]==1):
		next_do = raw_input('This table('+tableName+') had existed.\nWhat you want to do next? (C)ontinue or (Q)uit: ')
		if (next_do=='Q' or next_do=='q'):
			return -1
		else :
			return 0
	else :
		sql_create = "CREATE TABLE "+tableName+" (id integer PRIMARY KEY AUTOINCREMENT NOT NULL, resultIp varchar(15) NOT NULL, resultUrl varchar(150) NOT NULL, resultTitle varchar(50) NOT NULL)"
		cur.execute(sql_create)
		conn.commit()
	return tableName

def sql_insertCSResult(conn, cur, targetArgs, resultList, temp_ip):
	"""
	insert CS result to the table
	"""
	addr_dict = targetArgs.i.split('.')
	tableName = 'CSResult_'+addr_dict[0]+'_'+addr_dict[1]+'_'+addr_dict[2]
	for resultItem in resultList:
		sql_insert = "INSERT INTO "+tableName+" (`resultIp`, `resultUrl`, `resultTitle`) VALUES ('"+temp_ip+"', '"+resultItem[0]+"','"+resultItem[1]+"')"
		cur.execute(sql_insert)
		conn.commit()
		print sql_insert

def sql_getIpbyURL(conn, cur, targetArgs):
	"""
	get ip from data by url
	"""
	sql_getURL = "SELECT * FROM cs_urls WHERE csurl='"+targetArgs.u+"'"
	cur.execute(sql_getURL)
	conn.commit()
	URLitem = cur.fetchone()
	if (URLitem==None):
		return -1
	return URLitem[2]

def sql_isRSTableExist(conn, cur, targetArgs):
	"""
	check the result table is exist.
	"""
	addr_dict = targetArgs.i.split('.')
	tableName = 'CSResult_'+addr_dict[0]+'_'+addr_dict[1]+'_'+addr_dict[2]
	sql_isRSTExist = "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='"+tableName+"'"
	cur.execute(sql_isRSTExist)
	conn.commit()
	isExisted = cur.fetchone()
	if (isExisted[0]!=1):
		return -1
	return tableName

def sql_getRSList(conn, cur, RStableName):
	"""
	get the result list
	"""
	sql_getResultList = "SELECT * FROM " + RStableName
	cur.execute(sql_getResultList)
	conn.commit()
	resultList = cur.fetchall()
	return resultList

def sql_closeCur(cur):
	"""
	close cursor
	"""
	cur.close()

def sql_closeConn(conn):
	"""
	close cursor
	"""
	conn.close()